package com.tjresearch.redisdbtasktracker.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.tjresearch.redisdbtasktracker.pojo.ChartData;
import com.tjresearch.redisdbtasktracker.pojo.ItemInfo;

@Repository
public class DataDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
		
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<ChartData> getHighfreqData(Integer itemId) {
		String sql = "select hdate,value from highfreq_data where item_id = " + itemId + " order by hdate desc limit 0,180";
		List<ChartData> dataList = jdbcTemplate.query(sql, new RowMapper(){
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				ChartData data = new ChartData();
				data.setHdate(rs.getString("hdate"));
				data.setValue(rs.getDouble("value"));
				return data;
			}
		});
		Collections.reverse(dataList);
		return dataList;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<ItemInfo> getHighfreqItem() {
//		String sql = "select item_id,min(seq),item_name,datatype_id,indus_id,index_id from highfreq_item where datatype_id in (4,5) group by datatype_id,indus_id,index_id";
//		String sql = "select ite.item_id,min(ite.seq),ite.item_name,ite.datatype_id,ite.indus_id,ite.index_id,ind.indus_name from highfreq_item ite LEFT JOIN highfreq_bm_industry ind on ite.indus_id = ind.indus_id where datatype_id in (4,5) group by datatype_id,indus_id,index_id";
//		String sql = "select ite.item_id,min(ite.seq),ite.item_name,ite.datatype_id,ite.indus_id,ite.index_id,ind.indus_shortname,dex.index_name,type.datatype_name from highfreq_item ite LEFT JOIN highfreq_bm_industry ind on ite.indus_id = ind.indus_id LEFT JOIN highfreq_bm_index dex on ite.index_id = dex.index_id LEFT JOIN highfreq_datatype type on ite.datatype_id = type.datatype_id where ite.datatype_id in (4,5) group by datatype_id,indus_id,index_id";
		String sql = "select ite.item_id,min(ite.seq),ite.item_name,ite.datatype_id,ite.indus_id,ite.index_id,ind.indus_shortname,dex.index_name,type.datatype_name,catg.catg_name from highfreq_item ite LEFT JOIN highfreq_bm_industry ind on ite.indus_id = ind.indus_id LEFT JOIN highfreq_bm_index dex on ite.index_id = dex.index_id LEFT JOIN highfreq_datatype type on ite.datatype_id = type.datatype_id LEFT JOIN highfreq_bm_indus_catg_mapping map on ite.indus_id = map.indus_id LEFT JOIN highfreq_bm_indus_catg catg on map.catg_id = catg.catg_id where ite.datatype_id in (4,5)  and ite.seq=1 group by datatype_id,indus_id,index_id";
		List<ItemInfo> list = jdbcTemplate.query(sql,new RowMapper() {
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				ItemInfo itemInfo = new ItemInfo();
				itemInfo.setItemId(rs.getInt("item_id"));
				itemInfo.setItemName(rs.getString("item_name"));
				itemInfo.setDatatypeId(rs.getInt("datatype_id"));
				itemInfo.setIndusId(rs.getInt("indus_id"));
				itemInfo.setIndexId(rs.getInt("index_id"));
				itemInfo.setIndusName(rs.getString("indus_shortname"));
				itemInfo.setIndexName(rs.getString("index_name"));
				itemInfo.setDatatypeName(rs.getString("datatype_name"));
				itemInfo.setCatgName(rs.getString("catg_name"));
				return itemInfo;
			}
		});
		return list;
	}
	public String findAllIndusById(int indusId){
		String sql = "select indus_shortname from highfreq_bm_industry where indus_id="+indusId;
		@SuppressWarnings({ "unchecked", "rawtypes" })
		List<String> list = jdbcTemplate.query(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("indus_shortname");
			}
			
		});
		return list.get(0);
	}
	
	
}
